/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package BD;
import BE.Cliente;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import BE.Visita;
import org.hibernate.SQLQuery;
import org.hibernate.Transaction;
import org.hibernate.Session;

/**
 *
 * @author jcguzmanr
 */
public class VisitaBD {


    private Session session;

    public VisitaBD() {

         this.session = null;

    }

    public boolean insertarCliente(Visita objVisitaBE) {
         Transaction tx = null;
        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();
            tx = (Transaction) session.beginTransaction();
            session.save(objVisitaBE);
            tx.commit();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            if(tx!=null)
            {
                tx.rollback();
            }
            return false;
        }
    }

    
    public Integer VisitasRealizadas(int idUsuario){
        Integer id_vendedor=idUsuario;
        List lista = null;
        
        String consulta="";

        consulta="SELECT count(*) FROM visita WHERE srl_idvendedor="+id_vendedor+" and date_trunc('day',date_fechavisita) = date_trunc('day',now()) and srl_idcliente in";

        Date fecha=new Date();
        int intDia=fecha.getDay();
        String strDia="";
        switch (intDia) {
            case 0:  strDia="Domingo"; break;
            case 1:  strDia="Lunes"; break;
            case 2:  strDia="Martes"; break;
            case 3:  strDia="Miercoles"; break;
            case 4:  strDia="Jueves"; break;
            case 5:  strDia="Viernes"; break;
            case 6:  strDia="Sábado"; break;

        }        
        String consulta_hoja_ruta="";

        consulta_hoja_ruta="(Select srl_idpersona " +
                "From cliente where srl_idmesa in " +
                  "(select srl_idmesa " +
                  "from mesa " +
                  "where srl_idzona in " +
                     "(select srl_idzona " +
                      "from zona " +
                      "where srl_asignadoa="+id_vendedor+" and " +
                             "bool_eliminado=false) and " +
                             "bool_eliminado=false and " +
                             "cv_dia = '"+strDia+"') " +
                   "and bool_eliminado=false)";
        
        consulta=consulta+" "+consulta_hoja_ruta;
        Transaction tx =null;
        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();
            tx = session.beginTransaction();
            SQLQuery sqlquery=session.createSQLQuery(consulta);
            session.flush();
            lista = sqlquery.list();
            tx.rollback();
        } catch (Exception e) {
            e.printStackTrace();
            if(tx!=null)
            {
                tx.rollback();
            }
        }
        return Integer.parseInt(lista.get(0).toString());
    }
    public Integer PedidosRealizados(int idUsuario) {
//

        List lista = null;

        String consulta="";

        consulta="SELECT count(*) " +
                "FROM pedido " +
                "WHERE srl_registradopor=" + idUsuario +
                " and date_fecharegistro=date_trunc('day',now())";


        Transaction tx =null;
        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();
            tx = session.beginTransaction();
            SQLQuery sqlquery=session.createSQLQuery(consulta);
            session.flush();
            lista = sqlquery.list();
            tx.rollback();
        } catch (Exception e) {
            e.printStackTrace();
            if(tx!=null)
            {
                tx.rollback();
            }
        }
        return Integer.parseInt(lista.get(0).toString());
    }

    public Integer totalVisitasDia(int idUsuario){
        List lista = null;
        Date fecha=new Date();
        int intDia=fecha.getDay();
        String strDia="";
        switch (intDia) {
            case 0:  strDia="Domingo"; break;
            case 1:  strDia="Lunes"; break;
            case 2:  strDia="Martes"; break;
            case 3:  strDia="Miercoles"; break;
            case 4:  strDia="Jueves"; break;
            case 5:  strDia="Viernes"; break;
            case 6:  strDia="Sábado"; break;

        }
        Date dia= new Date();
        String consulta="";

        consulta="Select count(*) " +
                "From cliente where srl_idmesa in " +
                  "(select srl_idmesa " +
                  "from mesa " +
                  "where srl_idzona in " +
                     "(select srl_idzona " +
                      "from zona " +
                      "where srl_asignadoa="+idUsuario+" and " +
                             "bool_eliminado=false) and " +
                             "bool_eliminado=false and " +
                             "cv_dia = '"+strDia+"') " +
                   "and bool_eliminado=false";
        Transaction tx =null;
        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();
            tx = session.beginTransaction();
            SQLQuery sqlquery=session.createSQLQuery(consulta);
            //sqlquery.addEntity("cliente",Cliente.class);

            session.flush();
            lista = sqlquery.list();
            tx.rollback();
        } catch (Exception e) {
            e.printStackTrace();
            if(tx!=null)
            {
                tx.rollback();
            }
        }
        return Integer.parseInt(lista.get(0).toString());
    }

}
